package song.beans;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import song.db.ConnectionPoolBean;

public class HomeDaoBean {
	private ConnectionPoolBean pool;
	
	public HomeDaoBean(){
		
	}
	
	public HomeDaoBean(ConnectionPoolBean pool){
		this.pool = pool;
	}

	public void setPool(ConnectionPoolBean pool) {
		this.pool = pool;
	}
	
	public ArrayList<HomeDtoBean> selectAll(){
		
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<HomeDtoBean> list = new ArrayList<HomeDtoBean>();
		
		try {
			
			con = pool.getConnection();
			String sql = "select * from board";
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			
			while(rs.next()){
				HomeDtoBean homedto = new HomeDtoBean();
				homedto.setNum(rs.getInt(1));
				homedto.setWriter(rs.getString(2));
				homedto.setEmail(rs.getString(3));
				homedto.setTitle(rs.getString(4));
				homedto.setContent(rs.getString(5));
				homedto.setRegdate(rs.getTimestamp(6));
				
				list.add(homedto);
			}
			
			stmt.close();
			rs.close();
			pool.returnConnection(con);
			return list;
			
		} catch (SQLException se) {
			System.out.println(se.getMessage());
			return null;
		}
		
	}
	
	public HomeDtoBean selectView(String num){
		
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		HomeDtoBean homedto = new HomeDtoBean();
		
		try {
			
			con= pool.getConnection();
			String sql = "select * from board where num = "+num;
			stmt=con.createStatement();
			rs = stmt.executeQuery(sql);
			if(rs.next()){
				homedto.setNum(rs.getInt(1));
				homedto.setWriter(rs.getString(2));
				homedto.setEmail(rs.getString(3));
				homedto.setTitle(rs.getString(4));
				homedto.setContent(rs.getString(5));
				homedto.setRegdate(rs.getTimestamp(6));
			}
			
			
			stmt.close();
			rs.close();
			pool.returnConnection(con);
			return homedto;
		} catch (SQLException se) {
			System.out.println(se.getMessage());

			return null;
		}
		
	}
	
	public boolean update(HomeDtoBean fb){
		
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try {
			
			con = pool.getConnection();
			String sql = 	" update	board 				" +
							" set		email	=?, 		" +
							"			title	=?, 		" +
							"	  		content	=?, 		" +
							"			regdate	= sysdate	" +
							" where 	num 	=?			";
			
			pstmt = con.prepareStatement(sql);

			pstmt.setString(1, fb.getEmail());
			pstmt.setString(2, fb.getTitle());
			pstmt.setString(3, fb.getContent());
			pstmt.setInt(4, fb.getNum());
			int n = pstmt.executeUpdate();
			
			pstmt.close();
			pool.returnConnection(con);
			
			if(n > 0){
				return true;
			}else{
				return false;
			}
			
		} catch (SQLException se) {
			System.out.println(se.getMessage());
			return false;
		}
		
		
	}
	
	public boolean delete(String num){
		
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try {
			
			con = pool.getConnection();
			String sql = 	" delete from board where num="+num;
			
			pstmt = con.prepareStatement(sql);
			int n = pstmt.executeUpdate();
			
			pstmt.close();
			pool.returnConnection(con);
			
			if(n > 0){
				return true;
			}else{
				return false;
			}
			
		} catch (SQLException se) {
			System.out.println(se.getMessage());
			return false;
		}
		
	}
	
	public boolean insert(HomeDtoBean fb){
		
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try {
			
			con = pool.getConnection();
			String sql = 	" insert into board values(board_seq.nextval,?,?,?,?,sysdate)";
			
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, fb.getWriter());
			pstmt.setString(2, fb.getEmail());
			pstmt.setString(3, fb.getTitle());
			pstmt.setString(4, fb.getContent());
			
			int n = pstmt.executeUpdate();
			
			pstmt.close();
			pool.returnConnection(con);
			
			if(n > 0){
				return true;
			}else{
				return false;
			}
			
		} catch (SQLException se) {
			System.out.println(se.getMessage());
			return false;
		}
		
		
	}
}
